# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html

import psycopg2

# useful for handling different item types with a single interface
from itemadapter import ItemAdapter


class CatchfarmPipeline(object):

    def __init__(self):
        self.cursor = None
        self.conn = None
        self.conn = psycopg2.connect(database="postgres",
                                     user="postgres",
                                     password="postgres",
                                     host="127.0.0.1",
                                     port="5432")

        self.cursor = self.conn.cursor()
    def open_spider(self, spider):
        pass

    def process_item(self, item, spider):
        sql = "INSERT INTO postgres.farm_baidu(name, lat, lng, address, province, city, area, uid, geom) " \
              "VALUES ('{}', {}, {},'{}', '{}', '{}', '{}', '{}',st_point({},{},4326) ) on conflict(uid)  do nothing"
        sql = sql.format(item['name'], item['lat'], item['lng'], item['address'], item['province'], item['city'],
                         item['area'], item['uid'], item['lng'], item['lat'])
        print(sql)
        self.cursor.execute(sql)

        return item

    def close_spider(self, spider):
        self.conn.commit()
        self.cursor.close()
        # 关闭数据库连接
        self.conn.close()
